package com.byl.sdk.poi;

import com.byl.sdk.utils.StringUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

/**
 * Created by Administrator on 2020/1/10.
 */
public class ExcelUtils {

    public static String export(HttpServletRequest request,String title,List header,List column,List value){
        //创建工作薄对象
        HSSFWorkbook workbook=new HSSFWorkbook();//这里也可以设置sheet的Name
        //创建工作表对象
        HSSFSheet sheet = workbook.createSheet();
        int len=1;
        if(null!=header){
            len=header.size()-1;
        }
        //设置标题
        setTitle(workbook,sheet,title,len);
       //设置列
        setHeader(workbook,sheet,header);
        //设置值
        setColumnValue(workbook,sheet,column,value);

        String path="/download/"+System.currentTimeMillis();

        // 获得项目的路径
        ServletContext sc = request.getSession().getServletContext();
        String rootPath=sc.getRealPath("/");
        File file=new File(rootPath+path);
        if(!file.exists())
            file.mkdirs();
        //文档输出
        FileOutputStream out = null;
        try {
            out = new FileOutputStream(rootPath+path+"/志愿者登记表.xls");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        try {
            workbook.write(out);
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
       return path+"/志愿者登记表.xls";
    }

    private static void setColumnValue(HSSFWorkbook workbook,HSSFSheet sheet,List columnlist,List valuelist){
       if(null==valuelist||valuelist.size()==0)
           return;
        //字体样式
        CellStyle cellStyle=getCellStyle(workbook);
        CellStyle first=getCellStyle(workbook);
        first.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());// 设置背景色
        first.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        //创建行的单元格，从2开始
        for(int r=0;r<valuelist.size();r++){
            int rownum=r+2;
            HSSFRow row = sheet.createRow(rownum);
            row.setHeight((short)500);
            Map map= (Map) valuelist.get(r);
            for(int j=0;j<columnlist.size();j++){
                String col= (String) columnlist.get(j);
                String colValue="";
                if(col.equals("index")){
                    colValue=String.valueOf(r+1);
                }else{
                    Object tmp=map.get(col);
                    if(null!=tmp){
                        colValue=tmp.toString();
                    }
                }
                HSSFCell cell=row.createCell(j);
                cell.setCellValue(colValue);
                sheet.autoSizeColumn(j);
                sheet.setColumnWidth(j,sheet.getColumnWidth(j)*15/10);
                if(j==0){
                    cell.setCellStyle(first);
                }else{
                    cell.setCellStyle(cellStyle);
                }
            }
        }

    }



    private static CellStyle getCellStyle(HSSFWorkbook workbook){
        //字体样式
        CellStyle cellStyle=workbook.createCellStyle(); // 创建单元格样式
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  // 设置单元格水平方向对其方式
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 设置单元格垂直方向对其方式
        Font ft=workbook.createFont();
        ft.setFontName("楷体");
        ft.setFontHeightInPoints((short)12);
        cellStyle.setFont(ft);
        //设置边框线
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        return cellStyle;
    }

    private static void setHeader(HSSFWorkbook workbook,HSSFSheet sheet,List header){
        if(null==header||header.size()==0)
            return;
        //创建行的单元格，从1开始
        HSSFRow row = sheet.createRow(1);
        row.setHeight((short)500);
        //字体样式
        CellStyle cellStyle=workbook.createCellStyle(); // 创建单元格样式
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  // 设置单元格水平方向对其方式
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 设置单元格垂直方向对其方式
        Font ft=workbook.createFont();
        ft.setFontName("楷体");
        ft.setFontHeightInPoints((short)14);
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());// 设置背景色
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setFont(ft);
        //设置边框线
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        int len=header.size();
        for(int i=0;i<len;i++){
            Object obj=header.get(i);
            String headerName="";
            if(null!=obj){
                headerName=obj.toString();
            }
            HSSFCell cell=row.createCell(i);
            cell.setCellValue(headerName);
            sheet.autoSizeColumn(i);
            sheet.setColumnWidth(i,sheet.getColumnWidth(i)*30/10);
            cell.setCellStyle(cellStyle);
        }
    }

    /**
     * 设置标题
     * @param workbook
     * @param sheet
     * @param title
     * @param cols
     */
    private static void setTitle(HSSFWorkbook workbook,HSSFSheet sheet,String title,int cols){
        //创建行的单元格，从0开始
        HSSFRow row = sheet.createRow(0);
        //创建单元格
        HSSFCell cell=row.createCell(0);
        CellRangeAddress region=null;
        //设置标题
        if(!StringUtil.isBlank(title)){
            //设置行高
            row.setHeight((short)800);
            cell.setCellValue(title);
            region=new CellRangeAddress(0, 0, 0, cols);
            sheet.addMergedRegion(region);
            CellStyle cellStyle=workbook.createCellStyle(); // 创建单元格样式
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  // 设置单元格水平方向对其方式
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 设置单元格垂直方向对其方式
            Font font=workbook.createFont();
            font.setFontName("黑体");
            font.setFontHeightInPoints((short)22);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
        }
    }
}
